Dissertation Appendix A

Chapter 1: Agricultural Insurance Loss Analysis of the Pacific Northwest, 2001 to 2015

Appendix A documents the Chapter 1 exploratory data analysis process, examining the relationships of agricultural commodity loss, at a county level, from 1989-2015, across the three state region of the Pacific Northwest (Washington, Idaho, and Oregon), and then focus in on the 24 county region of the Inland Pacific Northwest (IPNW).

We examine the entire range of commodities and damage causes for the IPNW study area, identifying the top revenue loss commodities and their most pertinent damage causes, as indicated from the USDA’s agricultural commodity loss insurance archive. We also explore claim freqency, We fiinally zero in on the top 5 commodities for the region, and examine specific loss variation by year and damage cause.

Several steps were performed to examine the full set of data, and to narrow down factors to a usable form for further modeling analysis.

Step 1. Data Preparation. Here we load our agricultural insurance loss data.

Step 2. Data Preparation. Original Insurance Loss Dataset - Pacific Northwest

Step 3. Data Preparation. Aggregated Insurance Loss Dataset - Pacific Northwest

Step 4. Pacific Northwest (PNW) Overview. Here we examine overall commodity loss for the entire three state region of Washington, Idaho, and Oregon, which comes primarily from three primary agricultural regions - the IPNW (mostly Washington, with portions of northern Idaho and northeastern Oregon), the Southern Idaho Valley (Idaho), and the Willamette Valley (Oregon).

Step 5. PNW region overview of insurance loss by year, damage cause, and commodity, and county: 1989 to 2015. From the results of this examination, we narrow our time frame to 2001 to 2015.

Step 6. PNW region overview of insurance Loss claim counts by year, damage cause, commodity, and county: 2001-2015

Step 7. Refining our analysis to the 24-county inland Pacific Northwest (iPNW) Study Area.

Step 8. IPNW region overview of Insurance Loss by year, damage cause, commodity and county: 2001-2015

Step 9. IPNW region overview of Insurance Loss claim counts: 2001-2015

Step 10. APPLES: IPNW region overview of Insurance Loss 2001-2015

Step 11. BARLEY: IPNW region overview of Insurance Loss 2001-2015

Step 12. WHEAT: IPNW region overview of Insurance Loss 2001-2015

Step 13. CHERRIES: IPNW region overview of Insurance Loss 2001-2015

Step 14. PEAS: IPNW region overview of Insurance Loss 2001-2015

Step 1: Data Preparation

In order to generate any of the following tables and graphs, you will need to download the dataload.R file from the following location:

http://github.com/erichseamon/seamon_dissertation/data/

Run this dataload.R file locally. This will download all data and place it in your /tmp file. Then you will be able to re-generate any of the provided Rmarkdown files in:

http://github.com/erichseamon/seamon_dissertation/appendices/

the dataload.R file has a multitude of datasets, including:
* climatology summary data by county for the study area examined (inland Pacific Northwest);
* original RMA crop insurance data;
* state polygon shapefile;
* consumer price indexing;
* wheat yields; and
* wheat pricing


Step 2. Original Insurance Loss Dataset - Pacific Northwest

The following table is an example of the original data that was acquired from the USDA Risk Management Agency (RMA). Each record represents an individual insurance claim.

year state county commodity damagecause monthcode month acres loss lossperacre cropyear
11338 2001 ID Ada All Other Crops Drought 9 SEP 17.000 153.00 9.000000 2001
11339 2001 ID Ada All Other Crops Heat 8 AUG 105.200 5249.00 49.895437 2001
11340 2001 ID Ada All Other Crops Freeze 4 APR 125.000 4500.00 36.000000 2001
11341 2001 ID Ada All Other Crops Wind/Excess Wind 5 MAY 50.000 1800.00 36.000000 2001
11342 2001 ID Ada All Other Crops Wind/Excess Wind 4 APR 92.500 3330.00 36.000000 2001
11343 2001 ID Bannock WHEAT Drought 8 AUG 133.000 1212.00 9.112782 2001
11344 2001 ID Bannock WHEAT Drought 9 SEP 777.520 24807.00 31.905289 2001
11345 2001 ID Bannock WHEAT Drought 7 JUL 3529.754 54726.46 15.504327 2001
11346 2001 ID Bannock WHEAT Heat 7 JUL 19.796 2371.60 119.801980 2001
11347 2001 ID Bannock WHEAT Heat 8 AUG 25.000 904.00 36.160000 2001


Step 3. Aggregated Insurance Loss Dataset - Pacific Northwest

The following table is an example of an aggregated dataset derived from the original files. Here we have summarized claims by year, county, commodity, and damage cause. Each unique combination is summarized, which echos the total summarized loss, the number of claims, the total summarized acreage, loss per acre, loss per claim, and acres per claim. This dataset was the basis for our data examination.

year state county commodity damagecause loss count acres lossperacre lossperclaim acresperclaim
11 2014 ID Ada All Other Crops Area Plan Crops Only 1398 1 0 0.0000000 1398.0 0
12 2015 ID Ada All Other Crops Area Plan Crops Only 11810 1 0 0.0000000 11810.0 0
211 2008 OR Baker All Other Crops Area Plan Crops Only 15292 2 5482 2.7894929 7646.0 2741
212 2010 OR Baker All Other Crops Area Plan Crops Only 1819 2 2282 0.7971078 909.5 1141
215 2009 ID Bannock All Other Crops Area Plan Crops Only 2284 1 600 3.8066667 2284.0 600
245 2008 ID Bear Lake All Other Crops Area Plan Crops Only 8102 1 2468 3.2828201 8102.0 2468
246 2012 ID Bear Lake All Other Crops Area Plan Crops Only 7459 1 2468 3.0222853 7459.0 2468
291 2010 ID Bingham All Other Crops Area Plan Crops Only 4197 1 192 21.8593750 4197.0 192
292 2011 ID Bingham All Other Crops Area Plan Crops Only 7769 1 240 32.3708333 7769.0 240
293 2012 ID Bingham All Other Crops Area Plan Crops Only 6786 1 168 40.3928571 6786.0 168


Step 4: Pacific Northwest(PNW) Study Area

The following map shows the three key agricultural regions that make up over 95% of all agricultural insurance loss for the three state region of Washington, Oregon, and Idaho.



Step 5: PNW insurance claim loss summary

Here we examine the PNW region’s insurance loss by year, damage cause, and commodity, and county, from 1989 to 2015. From the results of this examination, we narrow our time frame to 2001 to 2015.

PNW total insurance loss by year, 1989-2015
1989-2015
damage cause loss
Drought 814496893.53
Decline in Price 406959628.09
Heat 307635976.72
Frost 235527722.93
Freeze 208770819.89
Hail 177157029.63
Excessive Moisture 165391940.25
Cold Wet Weather 109229260.25
Cold Winter 75956007.78
Failure Irrig Supply 61515359.03
PNW total insurance loss by year, 2001-2015
2001-2015
damage cause loss
Drought $723,039,146
Decline in Price $401,633,728
Heat $276,188,676
Frost $183,961,154
Freeze $167,008,613
Excess Moisture/Precip/Rain $156,789,247
Hail $150,625,615
Cold Wet Weather $98,760,529
Cold Winter $68,763,872
Failure Irrig Supply $58,570,602
PNW total insurance loss by year, 2001-2015
2001-2015
commodity loss
WHEAT $1,444,439,251
APPLES $156,063,380
CHERRIES $154,524,200
POTATOES $117,459,400
ADJUSTED GROSS REVENUE $116,781,042
BARLEY $102,125,046
DRY PEAS $58,038,162
All Other Crops $55,112,560
PNW total insurance loss by year, 2001-2015
2001-2015
year loss
2001 $77,233,486
2002 $104,897,463
2003 $114,966,090
2004 $52,018,936
2005 $72,875,470
2006 $68,982,845
2007 $87,943,728
2008 $160,537,412
2009 $567,460,556
2010 $106,794,738
2011 $125,625,761
2012 $95,809,547
2013 $177,199,860
2014 $270,956,814
2015 $313,739,035



Step 6: PNW insurance claim freqency summary

Below we examine the PNW insurance claim counts by the same factors: by commodity, damage cause, year, and county.

## Error: 'decode_colour' is not an exported object from 'namespace:farver'



Step 7: IPNW Study Area



Step 8: IPNW insurance claim loss summary



Step 9: IPNW insurance claim count summary

IPNW region total insurance loss by year, 2001-2015
2001-2015
year loss
2001 $51,203,203
2002 $73,905,306
2003 $70,314,803
2004 $43,387,412
2005 $45,054,042
2006 $50,022,732
2007 $47,237,969
2008 $117,324,601
2009 $436,808,728
2010 $51,023,916
2011 $65,883,241
2012 $46,062,127
2013 $116,089,278
2014 $174,745,874
2015 $237,671,277
IPNW region total insurance loss by commodity, 2001-2015
2001-2015
commodity loss
WHEAT $1,203,744,611
APPLES $89,186,728
CHERRIES $74,853,342
DRY PEAS $55,590,740
BARLEY $33,476,785
GRAPES $17,370,434
IPNW region total insurance loss by damage cause, 2001-2015
2001-2015
damage cause loss
Drought $673,854,997
Decline in Price $287,278,003
Heat $197,275,538
Frost $91,808,517
Hail $81,776,579
Freeze $78,563,950
Excessive Moisture $76,325,319
Cold Wet Weather $52,762,454
Cold Winter $33,445,556
Other Causes $19,094,006
Wind/Excess Wind $12,268,262



Step 10: APPLES, 2001-2015 for the IPNW

IPNW region APPLES total insurance loss by damage cause, 2001-2015
2001-2015
damagecause loss
Hail $40,971,994
Frost $17,016,586
Freeze $14,534,548
Cold Wet Weather $9,246,848
Wind/Excess Wind $2,934,044
Heat $2,422,480
Other $2,060,230
## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_apples_hailfrostfreeze$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_apples_hailfrostfreeze2013$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_apples_hailfrostfreeze2012$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_apples_hailfrostfreeze2006$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_apples_hailfrostfreeze2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found


Step 11: BARLEY, 2001-2015 for the IPNW

IPNW region BARLEY total insurance loss by damage cause, 2001-2015
2001-2015
damagecause loss
Drought $19,557,132
Excess Moisture/Precip/Rain $4,606,318
Heat $4,409,923
Hail $1,446,047
Frost $1,158,127
Other $936,234
Decline in Price $848,321
Cold Wet Weather $514,682
## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_barley_excessivemoisture2011$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_barley_droughtheat2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_barley_droughtheat$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found



Step 12: WHEAT, 2001-2015 for the IPNW

IPNW region WHEAT total insurance loss by damage cause, 2001-2015
2001-2015
damagecause loss
Drought $623,556,410
Decline in Price $242,258,102
Heat $149,599,349
Frost $45,091,012
Excess Moisture/Precip/Rain $31,923,877
Cold Winter $27,596,223
Freeze $27,064,747
Cold Wet Weather $23,086,130
Hail $19,621,554
Other $9,008,705
Plant Disease $2,925,149
Other (Snow-Lightning-Etc.) $2,013,353
## Error in names(x) <- value: 'names' attribute [4] must be the same length as the vector [3]
## Error in file(file, "rt"): cannot open the connection
## Error in subset(cpi, year >= 2001 & year <= 2015): object 'cpi' not found
## Error in eval(expr, envir, enclos): object 'cpi' not found
## Error in names(x) <- value: 'names' attribute [4] must be the same length as the vector [2]
## Error in plot.window(...): need finite 'xlim' values

## Error in max(wheatprice_all$price_adjusted):min(wheatprice_all$price): result would be too long a vector
## Error in max(wheatprice_all$price_adjusted):min(wheatprice_all$price): result would be too long a vector
## Error in file(file, "rt"): cannot open the connection
## Error in ggplot(wheatproduction, aes(fill = State, y = Value, x = Year)): object 'wheatproduction' not found
## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(WA_wheat_yields$Year, WA_wheat_yields$State, WA_wheat_yields$County, : object 'WA_wheat_yields' not found
## Error in colnames(WA_wheat_yields) <- c("year", "state", "county", "value"): object 'WA_wheat_yields' not found
## Error in tolower(WA_wheat_yields$county): object 'WA_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'WA_wheat_yields' not found
## Error in gsub(",", "", WA_wheat_yields$value): object 'WA_wheat_yields' not found
## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(ID_wheat_yields$Year, ID_wheat_yields$State, ID_wheat_yields$County, : object 'ID_wheat_yields' not found
## Error in colnames(ID_wheat_yields) <- c("year", "state", "county", "value"): object 'ID_wheat_yields' not found
## Error in tolower(ID_wheat_yields$county): object 'ID_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'ID_wheat_yields' not found
## Error in gsub(",", "", ID_wheat_yields$value): object 'ID_wheat_yields' not found
## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(OR_wheat_yields$Year, OR_wheat_yields$State, OR_wheat_yields$County, : object 'OR_wheat_yields' not found
## Error in colnames(OR_wheat_yields) <- c("year", "state", "county", "value"): object 'OR_wheat_yields' not found
## Error in tolower(OR_wheat_yields$county): object 'OR_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'OR_wheat_yields' not found
## Error in gsub(",", "", OR_wheat_yields$value): object 'OR_wheat_yields' not found
## Error in rbind(WA_wheat_yields, ID_wheat_yields, OR_wheat_yields): object 'WA_wheat_yields' not found
## Error in aggregate(iPNW_wheat_yields$value, by = list(iPNW_wheat_yields$county), : object 'iPNW_wheat_yields' not found
## Error in colnames(iPNW_wheat_yields_annual_average) <- c("NAME", "ave_yield"): object 'iPNW_wheat_yields_annual_average' not found
## Error in aggregate(iPNW_wheat_yields$value, by = list(iPNW_wheat_yields$year), : object 'iPNW_wheat_yields' not found
## Error in colnames(iPNW_wheat_yields_annual_average_barplot) <- c("Year", : object 'iPNW_wheat_yields_annual_average_barplot' not found
## Error in merge(counties, iPNW_wheat_yields_annual_average, by = c("NAME")): object 'iPNW_wheat_yields_annual_average' not found
## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_wheat_droughtheat$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_wheat_droughtheat2011$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_wheat_droughtheat2009$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_wheat_droughtheat2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(WA_wheat_yields$Year, WA_wheat_yields$State, WA_wheat_yields$County, : object 'WA_wheat_yields' not found
## Error in colnames(WA_wheat_yields) <- c("year", "state", "county", "value"): object 'WA_wheat_yields' not found
## Error in tolower(WA_wheat_yields$county): object 'WA_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'WA_wheat_yields' not found
## Error in gsub(",", "", WA_wheat_yields$value): object 'WA_wheat_yields' not found
## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(ID_wheat_yields$Year, ID_wheat_yields$State, ID_wheat_yields$County, : object 'ID_wheat_yields' not found
## Error in colnames(ID_wheat_yields) <- c("year", "state", "county", "value"): object 'ID_wheat_yields' not found
## Error in tolower(ID_wheat_yields$county): object 'ID_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'ID_wheat_yields' not found
## Error in gsub(",", "", ID_wheat_yields$value): object 'ID_wheat_yields' not found
## Error in file(file, "rt"): cannot open the connection
## Error in data.frame(OR_wheat_yields$Year, OR_wheat_yields$State, OR_wheat_yields$County, : object 'OR_wheat_yields' not found
## Error in colnames(OR_wheat_yields) <- c("year", "state", "county", "value"): object 'OR_wheat_yields' not found
## Error in tolower(OR_wheat_yields$county): object 'OR_wheat_yields' not found
## Error in lapply(X = X, FUN = FUN, ...): object 'OR_wheat_yields' not found
## Error in gsub(",", "", OR_wheat_yields$value): object 'OR_wheat_yields' not found
## Error in rbind(WA_wheat_yields, ID_wheat_yields, OR_wheat_yields): object 'WA_wheat_yields' not found
## Error in aggregate(iPNW_wheat_yields$value, by = list(iPNW_wheat_yields$county), : object 'iPNW_wheat_yields' not found
## Error in colnames(iPNW_wheat_yields_annual_average) <- c("NAME", "ave_yield"): object 'iPNW_wheat_yields_annual_average' not found
## Error in aggregate(iPNW_wheat_yields$value, by = list(iPNW_wheat_yields$year), : object 'iPNW_wheat_yields' not found
## Error in colnames(iPNW_wheat_yields_annual_average_barplot) <- c("Year", : object 'iPNW_wheat_yields_annual_average_barplot' not found
## Error in merge(counties, iPNW_wheat_yields_annual_average, by = c("NAME")): object 'iPNW_wheat_yields_annual_average' not found
## Error in eval(parse(text = paste("iPNW_yields_map$", "ave_yield", sep = ""))): object 'iPNW_yields_map' not found
## Error in structure(list(options = options), leafletData = data): object 'iPNW_yields_map' not found
## Error in getMapData(map): object 'map' not found
## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(WHEAT_damage_droughtheat_annual_ave_map$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found
## Error in eval(parse(text = paste("iPNW_yields_map$", "ave_yield", sep = ""))): object 'iPNW_yields_map' not found
## Error in pal(WHEAT_damage_droughtheat_annual_total_map$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found
## Error in eval(expr, envir, enclos): object 'iPNW_wheat_yields_annual_average_barplot' not found
## Error in eval(expr, envir, enclos): object 'iPNW_wheat_yields_annual_average_barplot' not found



Step 13: CHERRIES, 2001-2015 for the IPNW

IPNW region CHERRIES total insurance loss by damage cause, 2001-2015
2001-2015
damagecause loss
Excess Moisture/Precip/Rain $20,569,890
Frost $15,459,348
Freeze $14,625,022
Decline in Price $8,447,020
Cold Wet Weather $6,724,754
Heat $2,246,093
Hail $2,034,613
Other (Snow-Lightning-Etc.) $1,876,035
Other $1,467,713
Wind/Excess Wind $1,402,854
## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_cherries_moisturefrostfreeze$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_cherries_moisturefrostfreeze2013$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_cherries_moisturefrostfreeze2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found



Step 14: DRY PEAS, 2001-2015 for the IPNW

IPNW region DRY PEAS total insurance loss by damage cause, 2001-2015
2001-2015
damagecause loss
Excessive Moisture $22,316,191
Heat $10,205,657
Excess Moisture/Precip/Rain $8,046,188
Hail $5,171,205
Decline in Price $2,924,900
Cold Wet Weather $2,867,390
Other $2,064,864
Wind/Excess Wind $1,079,927
Frost $914,417
## Error: 'decode_colour' is not an exported object from 'namespace:farver'

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_drypeas_droughtheat2001_2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_drypeas_excessivemoisture2011$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found

## Error: 'decode_colour' is not an exported object from 'namespace:farver'
## Error in pal(county_drypeas_droughtheat2015$loss): could not find function "pal"
## Error in getMapData(map): object 'map' not found